package com.ndp.fb.rdb.db.impl;

import com.alibaba.dubbo.config.annotation.Service;
import com.ndp.fb.model.business.vo.cache.result.AudienceForPublish;
import com.ndp.fb.rdb.api.CustomAudienceRdbService;
import com.ndp.fb.rdb.db.DaoTemplate;
import com.ndp.fb.rdb.db.impl.mapper.DefaultRowMapper;
import com.ndp.fb.rdb.model.customaudience.CustomAudienceItem;
import com.ndp.fb.rdb.model.customaudience.CustomAudienceItemWithName;
import com.ndp.fb.util.ListUtil;
import com.ndp.fb.util.SQLUtil;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by Evan on 12/9/2015.
 */
@Service
public class CustomAudienceRdbServiceImpl extends DaoTemplate implements CustomAudienceRdbService {


    @Override
    public List<CustomAudienceItem> findByTmplId(List<Long> tmplId) {
        String sql = "SELECT * FROM custom_audience WHERE custom_audience_template_id IN  (" + SQLUtil.appendPlaceholder(tmplId.size()) + ") and is_delete = 0 ";
        return getJdbcTemplate().query(sql, tmplId.toArray(), DefaultRowMapper.autoRowMapper(CustomAudienceItem.class));
    }

    @Override
    public List<CustomAudienceItem> findByTemplateId(Long tmplId) {
        String sql = "SELECT * FROM custom_audience WHERE custom_audience_template_id = ? and is_delete = 0 ";
        return getJdbcTemplate().query(sql, new Object[]{tmplId}, DefaultRowMapper.autoRowMapper(CustomAudienceItem.class));
    }

    @Override
    public List<CustomAudienceItem> findAllByTmplId(List<Long> tmplId)
    {
        String sql = "SELECT * FROM custom_audience WHERE custom_audience_template_id IN  (" + SQLUtil.appendPlaceholder(tmplId.size()) + ") ";
        return getJdbcTemplate().query(sql, tmplId.toArray(), DefaultRowMapper.autoRowMapper(CustomAudienceItem.class));
    }

    @Override
    public List<AudienceForPublish> findPassedByAccountId(List<Long> accountIds, Long appId) {
        String sql = "SELECT GROUP_CONCAT(a.custom_audience_id) AS custom_audience_id,GROUP_CONCAT(a.fb_account_id) AS fb_account_id,b.name,b.subtype,b.country_code FROM custom_audience a,custom_audience_template b,custom_audience_template_fb_connection_object c " +
                "WHERE a.custom_audience_template_id = b.id AND b.id = c.custom_audience_template_id  AND a.delivery_status_code=200 AND a.operation_status_code=200 AND a.is_delete=0 AND a.fb_account_id IN (" + SQLUtil.appendPlaceholder(accountIds.size()) + ") AND b.is_delete=0\n" +
                "AND c.fb_connection_object_id= "+ appId +" GROUP BY b.id ORDER BY a.fb_account_id";
        return getJdbcTemplate().query(sql, accountIds.toArray(), DefaultRowMapper.autoRowMapper(AudienceForPublish.class));
    }

    @Override
    public List<CustomAudienceItem> findByTemplateIdAndAccountId(Long templateId, Long accountId) {
        String sql = "SELECT * from custom_audience ca where ca.fb_account_id = ? and ca.custom_audience_template_id = ? and is_delete = 0";
        return getJdbcTemplate().query(sql,new Object[]{accountId,templateId},DefaultRowMapper.autoRowMapper(CustomAudienceItem.class));
    }

    @Override
    public List<Long> findByAccountId(Long accountId) {
        String sql = "SELECT custom_audience_id FROM custom_audience WHERE fb_account_id=? and custom_audience_id !=-1  and is_delete = 0";
        return getJdbcTemplate().queryForList(sql, new Object[]{accountId}, Long.class);
    }

    @Override
    public CustomAudienceItemWithName findByCustomAudienceId(Long fbCustomAudienceId) {
        return findOne("SELECT c.*,t.name FROM custom_audience c JOIN custom_audience_template t ON c.custom_audience_template_id = t.id  WHERE c.custom_audience_id = ? AND c.is_delete=0",
                new Object[]{fbCustomAudienceId}, DefaultRowMapper.autoRowMapper(CustomAudienceItemWithName.class));
    }

    @Override
    public void updateIsDelete(List<Long> customAudienceIds,String reason) {
        String sql = "UPDATE custom_audience SET " +
                "is_delete = 1,reason = ? WHERE 1=1 AND " +
                "custom_audience_id = ?";
        getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setObject(1, reason);
                ps.setObject(2, customAudienceIds.get(i));
            }
            @Override
            public int getBatchSize() {
                return customAudienceIds.size();
            }
        });
    }

    @Override
    public void updateByCustomAudienceId(CustomAudienceItem item) {
        String sql = "UPDATE custom_audience SET delivery_status_code = ?, delivery_status_description = ?, operation_status_code= ?, operation_status_description= ?, time_updated = ?   WHERE 1=1 AND custom_audience_id = ?";
        getJdbcTemplate().update(sql, new Object[]{item.getDeliveryStatusCode(), item.getDeliveryStatusDescription(), item.getOperationStatusCode(), item.getOperationStatusDescription(), item.getTimeUpdated(), item.getCustomAudienceId()});
    }

    @Override
    public List<Long> batchSaveCustomAudience(final List<CustomAudienceItem> customAudienceItemList) {
        return batchSaveResultId(customAudienceItemList);
    }

    @Override
    public Long saveCustomAudience(final CustomAudienceItem customAudienceItem) {
        return saveResultId(customAudienceItem);
    }


    @Override
    public void updateRequestQueueStatus(Long customAudienceDbId, int i) {
        String sql = "UPDATE custom_audience SET request_queue_status = ? WHERE custom_audience_id = ?";
        getJdbcTemplate().update(sql, new Object[]{i, customAudienceDbId});
    }

    @Override
    public void updateRequestQueueStatusById(Long id, int status) {
        String sql = "UPDATE custom_audience SET request_queue_status = ? WHERE id = ?";
        getJdbcTemplate().update(sql, new Object[]{status, id});
    }


    @Override
    public void updateVersionAndRequestQueueStatus(Long customAudienceDbId, String version, int status) {
        String sql = "UPDATE custom_audience SET version = ? ,request_queue_status = ? WHERE id = ?";
        getJdbcTemplate().update(sql, new Object[]{version, status, customAudienceDbId});
    }

    @Override
    public void updateVersionAndRequestQueueStatusAndFBStatus(Long customAudienceDbId, String version, int status) {
        String sql = "UPDATE custom_audience SET version = ? ,request_queue_status = ? ,delivery_status_code = -1 ,delivery_status_description = '',operation_status_code = -1 ,operation_status_description = '' WHERE id = ?";
        getJdbcTemplate().update(sql, new Object[]{version, status, customAudienceDbId});
    }

    @Override
    public void updateDeliveryStatusDescriptionAndStatus(Long customAudienceDbId,int status,String description){
        String sql = "UPDATE custom_audience SET delivery_status_code = ? ,delivery_status_description = ? WHERE id = ?";
        getJdbcTemplate().update(sql, new Object[]{status, description, customAudienceDbId});
    }

    @Override
    public void updateRequestQueueStatus(String customAudienceDbId, String version, int status) {
        String sql = "UPDATE custom_audience SET request_queue_status = ? WHERE id = ? and version = ?";
        getJdbcTemplate().update(sql, new Object[]{status, customAudienceDbId, version});
    }


    @Override
    public void updateFbCustomAudienceId(Long id, Long fbCustomAudienceId)
    {
        String sql = "UPDATE custom_audience SET  custom_audience_id = ?  WHERE id = ?";
        getJdbcTemplate().update(sql, new Object[]{fbCustomAudienceId, id});
    }

    @Override
    public void updateFbCustomAudienceIdAndStatus(Long id, Long fbCustomAudienceId, int status) {
        String sql = "UPDATE custom_audience SET  custom_audience_id = ? , request_queue_status = ?  WHERE id = ?";
        getJdbcTemplate().update(sql, new Object[]{fbCustomAudienceId, status, id});
    }

    @Override
    public void delByTmplIds(List<Long> tmplIds,String delReason)
    {
        String sql = "UPDATE custom_audience SET " +
                "is_delete = 1,del_reason = ? WHERE 1=1 AND " +
                "custom_audience_template_id = ?";
        getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setObject(1, delReason);
                ps.setObject(2, tmplIds.get(i));
            }

            @Override
            public int getBatchSize() {
                return tmplIds.size();
            }
        });
    }

    @Override
    public List<CustomAudienceItem> getCustomAudienceItemListByTemplateId(String userId,Long customAudienceTemplateId) {
        String sql  = "select ea.name as fb_account_name,ca.* \n" +
                "from custom_audience ca,entrust_account ea\n" +
                "where ea.fb_account_id = ca.fb_account_id and ca.custom_audience_template_id = ? and ca.is_delete = 0 \n" +
                "and ca.fb_account_id in(\n" +
                "select distinct fb_account_id from fb_userpermissions_adaccounts fua,fb_user fu,user u where  fua.`fb_user_id` = fu.fb_user_id  and u.ticket = fu.user_id and u.ticket = ? and fua.status = 'active' AND fua.role IN ('GENERAL_USER', 'ADMIN') \n" +
                ")";
        return getJdbcTemplate().query(sql,new Object[]{customAudienceTemplateId,userId},DefaultRowMapper.autoRowMapper(CustomAudienceItem.class));
    }

    @Override
    public CustomAudienceItem findById(Long id) {
        String sql = "select * from custom_audience where id = ? and is_delete = 0";
        return findOne(sql,new Object[]{id},DefaultRowMapper.autoRowMapper(CustomAudienceItem.class));
    }


    @Override
    public void deleteByFbConnObjIds(List<Long> fbConnObjIds,String delReason) {
        String sql = "SELECT custom_audience_template_id FROM custom_audience_template_fb_connection_object WHERE is_delete = 0 AND fb_connection_object_id IN (" + SQLUtil.appendPlaceholder(fbConnObjIds.size()) + ")";
        List<Long> tmplIds = getJdbcTemplate().queryForList(sql, fbConnObjIds.toArray(), Long.class);
        if(ListUtil.isEmpty(tmplIds))
            return;
        delByTmplIds(tmplIds,delReason);
    }


}
